﻿using System;
using System.Data.SqlClient;
using InterpriseSuiteEcommerceCommon.DTO;
using InterpriseSuiteEcommerceCommon.Extensions;

namespace InterpriseSuiteEcommerceCommon.DataAccess
{
    public class ActiveShopperDA
    {
        public static bool IsShopperCodeNotUsed(string customerCode)
        {
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();
                var reader = DB.GetRS("select 1 from EcommerceActiveShoppers with (NOLOCK) where cast(customerCode as nvarchar(50)) = " + DB.SQuote(customerCode), con);
                bool isUsed = reader.Read();
                reader.Close();
                con.Close();
                return !isUsed;
            }
        }

        public static void DeleteInactiveShoppers()
        {
            int? timeLimit = AppLogic.AppConfig("WebSupport.Time").TryParseInt();
            if (!timeLimit.HasValue) { timeLimit = 3; }

            string currentTime = DateTime.Now.ToString();
            string sql = string.Format("DELETE EcommerceActiveShoppers WHERE DATEDIFF(hour, CONVERT(VARCHAR(50), TimeStarted, 22), CONVERT(VARCHAR(50), {0}, 22)) >= {1}",
                                            DB.SQuote(currentTime),
                                            timeLimit.Value.ToString()
                                      );
            DB.ExecuteSQL(sql);
        }

        public static void DeleteShoppersByRequestCode(string requestCode)
        {
            DB.ExecuteSQL(string.Format("DELETE EcommerceActiveShoppers WHERE [RequestCode] = {0}", DB.SQuote(requestCode)));
        }

        public static string GetRequestCodeByCustomerCode(string customerCode)
        {
            string code = string.Empty;
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();
                var reader = DB.GetRS("select RequestCode from EcommerceActiveShoppers with (NOLOCK) where cast(customerCode as nvarchar(50)) = " + DB.SQuote(customerCode), con);
                if (reader.Read())
                {
                    code = reader["RequestCode"].ToString();
                }

                reader.Close();
                con.Close();
                return code;
            }
        }

        /// <summary>
        /// ActiveShoppersID Cannot be null
        /// </summary>
        /// <param name="activeShopper"></param>
        public static void AddActiveShoppers(ActiveShopper activeShopper)
        {
            DB.ExecuteSQL(string.Format("INSERT INTO EcommerceActiveShoppers(ActiveShoppersId, RequestCode, CustomerCode, ContactGuid, WebsiteCode, TimeStarted, DateCreated) values({0},{1},{2},{3},{4},{5},{6})",
                            DB.SQuote(activeShopper.ActiveShopperId),
                            DB.SQuote(activeShopper.RequestCode),
                            DB.SQuote(activeShopper.CustomerCode),
                            DB.SQuote(activeShopper.ContactGuid.ToString()),
                            DB.SQuote(activeShopper.WebsiteCode),
                            DB.SQuote(activeShopper.TimeStarted.ToString()),
                            DB.SQuote(DateTime.Now.ToString())));
        }

        public static Guid? GetContactCodeByActiveShoppersCode(string code)
        {
            Guid? contactCode = null;
            using (var con = new SqlConnection(DB.GetDBConn()))
            {
                con.Open();
                var reader = DB.GetRS(string.Format("SELECT ContactGuid FROM EcommerceActiveShoppersView WHERE RequestCode = {0}", DB.SQuote(code)), con);
                if (reader.Read())
                {
                    string guid = reader["ContactGuid"].ToString();
                    contactCode = new Guid(guid);
                }
                reader.Close();
                con.Close();
            }

            return contactCode;
        }
    }
}
